﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;

namespace CNotebook.Sales
{
    public class DeliveryManager:ConnectionManager
    {
        #region Others
        public bool IsReferenceSIDRNoExists(string _DOCUMNT_CODE, string _DIVREFERENCE)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM ODLN WHERE DOCUMENTCODE=@DocumentCode AND DivReference=@DivReference";
            oCommand.Parameters.Add(new SqlParameter("@DocumentCode", _DOCUMNT_CODE));
            oCommand.Parameters.Add(new SqlParameter("@DivReference", _DIVREFERENCE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public SqlException SetDeliveryInProcess(string sInvoiceNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='In-Process' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sInvoiceNo));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ODLN WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Adding/Updating Invoice
        public SqlException AddDelivery(DeliveryUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO ODLN (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, StoreCode, CardCode, CardName, ContactPerson, PaymentTerms, Reference, CustRefPO, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, WithHoldingTax, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, Division, DocumentCode, DivReference, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                            " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @StoreCode, @CardCode, @CardName, @ContactPerson, @PaymentTerms, @Reference, @CustRefPO, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @WithHoldingTax, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, @Division, @DocumentCode, @DivReference, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
                oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
                oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
                oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
                oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



                oCommand.ExecuteNonQuery();

                #region Line Items
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO DLN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, LotNumber, LastStockQty, Qty, CQty, PriceListCode, UnitPrice, UnitCost, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuID) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @LotNumber, @LastStockQty, @Qty, @CQty, @PriceListCode, @UnitPrice, @UnitCost, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuID)";



                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));

                        if (oRow["QtyOrder"].ToString() == "")
                        {
                            oRow["QtyOrder"] = "0";
                        }

                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", Convert.ToString(oRow["LotNumber"].ToString())));
                        if (oRow["QtyOrder"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@QtyOrder", 0));
                        }
                        if (oRow["CQtyOrder"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@CQtyOrder",1));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@LastStockQty", Convert.ToDouble(oRow["LastStockQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));


                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }

                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemGuID", oRow["ItemGuID"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                }
                #endregion

                
                #region Accounting Entries

                if (oUnit.AccountingEntries != null)
                {
                    foreach (DataRow oRow in oUnit.AccountingEntries.Rows)
                    {
                        if (oRow.RowState != DataRowState.Deleted)
                        {
                            oCommand = new SqlCommand();

                            oCommand.Connection = this.Connection;
                            oCommand.CommandType = CommandType.Text;

                            oCommand.CommandText = "INSERT INTO [DLN3] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";

                            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                            oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                            oCommand.ExecuteNonQuery();
                        }
                    }
                }


                #endregion
                

                return null;
            }
            catch(SqlException ex)
            {
                return ex;
            }

        }

        public SqlException UpdateDelivery(DeliveryUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ODLN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, CustRefPO=@CustRefPO, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DivReference=@DivReference, DocumentCode=@DocumentCode, Division=@Division, ModifiedBy=@ModifiedBy, DateModified=GetDate()" +
                                                            " WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));

                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));

                oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
                oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE DLN1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Line Items
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO DLN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, LotNumber, LastStockQty, Qty, CQty, PriceListCode, UnitPrice,  UnitCost, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @LotNumber, @LastStockQty, @Qty, @CQty, @PriceListCode, @UnitPrice, @UnitCost, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";

                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@LastStockQty", Convert.ToDouble(oRow["LastStockQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }

                }

                #endregion

                #region Updating Accounting Entries


                oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "DELETE [INV3] WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                if (oUnit.AccountingEntries != null)
                {
                    foreach (DataRow oRow in oUnit.AccountingEntries.Rows)
                    {
                        if (oRow.RowState != DataRowState.Deleted)
                        {
                            oCommand = new SqlCommand();

                            oCommand.Connection = this.Connection;
                            oCommand.CommandType = CommandType.Text;

                            oCommand.CommandText = "INSERT INTO [DLN3] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";

                            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                            oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                            oCommand.ExecuteNonQuery();
                        }
                    }

                }


                #endregion


                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }

        }

        public SqlException AddDistributionEntries(string DocumentNumber, DataTable dtAccountingEntries)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                #region Accounting Entries


                oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "DELETE [DLN3] WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in dtAccountingEntries.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandType = CommandType.Text;

                        oCommand.CommandText = "INSERT INTO [DLN3] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }

        public void SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ODLN SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT.Replace(",","")));

            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Post/Cancel/Close Invoice
        public void PostDocument(string _DOCNUMBER, string PostedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='Posted', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", PostedBy));
            oCommand.ExecuteNonQuery();


        }

        public void DeliverOrder(string _WAREHOUSE, string _SALES_ORDER_NO, string _DOCNUMBER, string DeliveredBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='Delivered', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", DeliveredBy));
            oCommand.ExecuteNonQuery();


            #region Applying Deduction to Inventory
            string selectSQL = "SELECT * FROM DLN1 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), ((Convert.ToDouble(oRow["Qty"]) * Convert.ToDouble(oRow["CQty"])) * -1).ToString());
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), ((Convert.ToDouble(oRow["Qty"]) * Convert.ToDouble(oRow["CQty"])) * -1).ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString());
                }
            }
            #endregion

            #region Updating Sales Order Remarks

            if (_SALES_ORDER_NO.Trim() != "")
            {
                #region Getting the Line Items of SalesOrder
                OrderManager oSOManager = new OrderManager();
                oSOManager.Connection = this.Connection;
                DataTable dtSO = new DataTable();

              
                dtSO = oSOManager.GetOrderLineItems(_SALES_ORDER_NO);

                #endregion

                int iStatus = 2; // 1 = Open, 2 = Closed
                
                foreach (DataRow oRow in dtSO.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "SELECT CASE WHEN SUM(Qty) IS NULL THEN 0 ELSE SUM(Qty) END Qty FROM ODLN JOIN DLN1 ON ODLN.DOCNUM = DLN1.DOCNUM WHERE DLN1.ItemGuid = @ItemGuid AND (ODLN.DOCSTATUS <> 'Draft' AND ODLN.DocStatus <> 'Canceled')";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    DataTable dtDLN2 = new DataTable();
                    SqlDataAdapter dAdapter = new   SqlDataAdapter();
                    dAdapter.SelectCommand  = oCommand;
                    dAdapter.Fill(dtDLN2);

                    if (dtDLN2.Rows.Count > 0)
                    {
                        DataRow drRowDLN2 = dtDLN2.Rows[0];

                        if (Convert.ToDouble(oRow["Qty"]) != Convert.ToDouble(drRowDLN2["Qty"]))
                        {
                            iStatus = 1;
                            break;
                        }
                    }
                    else
                    {
                        iStatus = 1;
                        break;
                    }
                    



                }

                if (iStatus == 2)
                {
                    oSOManager.CloseOrder(_SALES_ORDER_NO);
                }


                if (iStatus == 1)
                {
                    oSOManager.SetOrderOpen(_SALES_ORDER_NO);
                }


            }

            
            #endregion

        }

        public void CancelDR(string sOrderNo, string CancelledBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='Canceled', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", CancelledBy));

            oCommand.ExecuteNonQuery();
        }

        public void CloseDR(string _INVOICE_NO, string ClosedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='Closed', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _INVOICE_NO));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", ClosedBy));
            oCommand.ExecuteNonQuery();
        }

        public void SetDROpen(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='Open' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.ExecuteNonQuery();
        }

        public SqlException SetDRInProcess(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ODLN SET DOCSTATUS='In-Process' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        #endregion

        #region Retreiving

        public DataTable GetAccountingDistribution(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM DLN3 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        // Getting Invoices - Search
        public DataTable GetDeliveries(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE DocNum LIKE @DOCNUM ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesByRefSONo(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE Reference LIKE @DOCNUM ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesBySIDRNo(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM VW_DELIVERIES_BY_DRSINo WHERE SIDRNo LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesByCustRefPO(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE CustRefPO LIKE @DOCNUM ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable GetDeliveriesByBranchName(string sCustomerName)
        {
            sCustomerName = sCustomerName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE CardName LIKE @DOCNUM ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sCustomerName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetDeliveriesByBranchCode(string _CUST_CODE)
        {
            _CUST_CODE = _CUST_CODE.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE CardCode LIKE @CardCode ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CUST_CODE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        // Getting Invoices - Search with Balance

        public DataTable GetDeliveriesWithBalance(string OutletCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND DocNum LIKE @DOCNUM AND DOCSTATUS='Posted' AND CardCode=@CardCode ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesBySIDRNoWithBalance(string OutletCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND DivReference LIKE @DOCNUM AND DOCSTATUS='POSTED'  AND CardCode=@CardCode ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesByCustRefPOWithBalance(string OutletCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND CustRefPO LIKE @DOCNUM  AND DOCSTATUS='POSTED'  AND CardCode=@CardCode ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesWithBalance(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND DocNum LIKE @DOCNUM AND DOCSTATUS='Posted' ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesBySIDRNoWithBalance(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND DivReference LIKE @DOCNUM AND DOCSTATUS='POSTED' ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesByCustRefPOWithBalance(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND CustRefPO LIKE @DOCNUM  AND DOCSTATUS='POSTED' ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveriesByBranchWithBalance(string sCustomerName)
        {
            sCustomerName = sCustomerName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND CardName LIKE @DOCNUM  AND DOCSTATUS='POSTED' ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sCustomerName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetDeliveriesByBranchCodeWithBalance(string _CUST_CODE)
        {
            _CUST_CODE = _CUST_CODE.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE (NetAmount - AppliedAmount) > 0 AND CardCode LIKE @CardCode  AND DOCSTATUS='POSTED' ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CUST_CODE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        //---------------------------------------------------------------------

        public DataRow GetDelivery(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DocumentCode + ' ' +  DivReference as SIDRNo, *, NetAmount - AppliedAmount TotalBalance FROM ODLN WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetDeliveryLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM DLN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

        #region Report Retreivable

        public DataTable GetDeliveryLogsPerDocument(DateTime _FROM, DateTime _TO, bool _Draft, bool _Delivered, bool _Posted, bool _Open, bool _Closed, bool _InProcess)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_DR";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHDELIVERED", Convert.ToInt16(_Delivered   )));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));
            oCommand.Parameters.Add(new SqlParameter("@WITHINPROCESS", Convert.ToInt16(_InProcess)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDeliveryLogsPerItems(DateTime _FROM, DateTime _TO, bool _Draft, bool _Delivered, bool _Posted, bool _Open, bool _Closed, bool _InProcess)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_DR_PER_ITEM";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHDELIVERED", Convert.ToInt16(_Delivered)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));
            oCommand.Parameters.Add(new SqlParameter("@WITHINPROCESS", Convert.ToInt16(_InProcess)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }


        public DataTable GetAgingInvoices(int IsByDeliveryDate, DateTime _ASOF)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_AGING_REPORT";
            oCommand.Parameters.Add(new SqlParameter("@ASOF", _ASOF));
            oCommand.Parameters.Add(new SqlParameter("@ByDeliveryDate", IsByDeliveryDate));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDRStatusByDeliveryDate(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDRStatusByPostingDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS_BY_POSTINGDATE";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));
            
            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDRStatusByInvoiceDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS_BY_INVOICEDATE";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }


        #endregion
    }
}

